package com.apes.fn.tms.repository;

import com.apes.fn.tms.model.WaybillPlanningLog;
import com.apes.framework.jpa.repository.CustomRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;
import java.util.Map;

public interface WaybillPlanningLogRepository extends CustomRepository<WaybillPlanningLog, String> {

    @Query(value = " SELECT ID, TO_CHAR(A.CREATE_DATE, 'YYYY-MM-DD HH24:MI:SS') CREATE_DATE, DELIVERY_ADDRESS, " +
            "        (SELECT NAME FROM MAS_PHY_DEPOT WHERE ID = A.DELIVERY_ADDRESS) DELIVERY_ADDRESS_NAME, " +
            "        SHIPPING_ADDRESS, " +
            "        (SELECT NAME FROM MAS_PHY_DEPOT WHERE ID = A.SHIPPING_ADDRESS) SHIPPING_ADDRESS_NAME, " +
            "       (SELECT MAX(ESTIMATED_DELIVERY_TIME) ESTIMATED_DELIVERY_TIME " +
            "           FROM TMS_WAYBILL_ROUTE_PLANNING J, TMS_WAYBILL_PLANNING_PLAN K " +
            "          WHERE J.PLAN_NUMBER = K.WAYBILL_ID " +
            "            AND J.WAYBILL_ID = K.WAYBILL_ID " +
            "            AND A.ID = K.WAYBILL_ID) ESTIMATED_DELIVERY_TIME, " +
            "       TO_CHAR(A.CREATE_DATE, 'YYYY-MM-DD HH24:MI:SS') START_DATE, A.RECEIVED_DATE, " +
//            "       TO_CHAR(A.CREATE_DATE, 'YYYY-MM-DD HH24:MI:SS'), " +
            "       (NVL(TO_DATE(TO_CHAR(A.RECEIVED_DATE, 'YYYY-MM-DD HH24:MI:SS'), " +
            "                     'YYYY-MM-DD HH24:MI:SS'), SYSDATE) - " +
            "        TO_DATE(TO_CHAR(A.CREATE_DATE, 'YYYY-MM-DD HH24:MI:SS'), " +
            "                 'YYYY-MM-DD HH24:MI:SS')) * 24 * 60 HS, " +
            "       (SELECT NAME " +
            "           FROM SOE_BUSINESS_ATTR_VALUE " +
            "          WHERE BUSINESS_ATTR_ID = 'Waybill.transportStatus' " +
            "            AND CODE = A.TRANSPORT_STATUS) TRANSPORT_STATUS " +
            "  FROM TMS_WAYBILL A " +
            " WHERE ID = :waybill_id " +
            " ORDER BY ID DESC ", nativeQuery = true)
    List<Map> findMainWaybillPlanningLog(@Param("waybill_id") String waybill_id);

    @Query(value = " SELECT * FROM (SELECT A.ID, A.WAYBILL_ID, (SELECT CAR_INFOMATION_ID FROM TMS_CAR_DETAILS WHERE ID = A.CAR_DETAILS_ID) CAR_INFORMATION_ID, " +
            "        A.CREATE_DATE, TO_CHAR(A.CREATE_DATE, 'YYYY-MM-DD HH24:MI:SS') || ',' || A.NOTES CONTENTS_ " +
            "   FROM TMS_WAYBILL_PLANNING_LOG A " +
            "  WHERE A.WAYBILL_ID = :waybill_id " +
            " ) ORDER BY CREATE_DATE, CAR_INFORMATION_ID ", nativeQuery = true)
    List<Map> findWaybillPlanningLog(@Param("waybill_id") String waybill_id);
}
